package com.junzhiit.nengji.dao.impl;

import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import com.bainaeco.framework.base.dao.AbstractBaseDAO;
import com.bainaeco.framework.common.jdbc.StatementParameter;
import com.bainaeco.framework.enums.IsDelete;
import com.junzhiit.nengji.bo.UserEvaluateBO;
import com.junzhiit.nengji.bo.UserEvaluateCountBO;
import com.junzhiit.nengji.dao.UserEvaluateDAO;
import com.junzhiit.nengji.model.UserEvaluate;
import com.junzhiit.nengji.model.enums.Is;
import com.junzhiit.nengji.model.enums.UserEvaluateScore;
import com.junzhiit.nengji.model.enums.UserEvaluateSeachStatus;

@Repository
public class UserEvaluateDAOImpl extends AbstractBaseDAO<UserEvaluate> implements UserEvaluateDAO {

	@Override
	public List<UserEvaluateBO> findAll(UserEvaluate userEvaluate, Integer scoreState, int pageNumber, int maxPageRow) {

		StringBuffer sql = new StringBuffer();
		sql.append(" select");
		sql.append(" u.avatar_url,u.`name`,u.nick_name,");
		sql.append(" ue.content,ue.image,ue.score,ue.created_time,");
		sql.append(" sc.standards,");
		sql.append(" og.goods_name,og.sku_content_name");
		sql.append(" from user_evaluate ue");
		sql.append(" left join `user` u on u.id=ue.user_id");
		sql.append(" left join orders_goods og on og.id=ue.orders_goods_id");
		sql.append(" left join sku_content sc on sc.id=og.sku_content_id");
		sql.append(" where ue.is_delete=? and ue.shop_id=?");

		StatementParameter param = new StatementParameter();
		param.setInt(IsDelete.NO.toIntValue());
		param.setString(userEvaluate.getShopId());

		if (StringUtils.isNotBlank(userEvaluate.getGoodsId())) {
			sql.append(" and ue.goods_id=? ");
			param.setString(userEvaluate.getGoodsId());
		}
		if (scoreState != null) {
			switch (scoreState) {
			case 0:
				sql.append(" and score<=? and score>=?");
				param.setDouble(2.5d);
				param.setDouble(1d);
			case 1:
				sql.append(" and score<? and score>?");
				param.setDouble(4d);
				param.setDouble(2.5d);
			case 2:
				sql.append(" and score<=? and score>=?");
				param.setDouble(5d);
				param.setDouble(4d);
			}
		}

		sql.append(" order by ue.created_time desc");

		return queryForPagingList(sql.toString(), UserEvaluateBO.class, param, pageNumber, maxPageRow);
	}

	@Override
	public int getTotalCount(UserEvaluate userEvaluate, Integer scoreState) {

		StringBuffer sql = new StringBuffer();
		sql.append(" select");
		sql.append(" count(ue.id)");
		sql.append(" from user_evaluate ue");
		sql.append(" left join `user` u on u.id=ue.user_id");
		sql.append(" left join orders_goods og on og.id=ue.orders_goods_id");
		sql.append(" left join sku_content sc on sc.id=og.sku_content_id");
		sql.append(" where ue.is_delete=? and ue.shop_id=?");

		StatementParameter param = new StatementParameter();
		param.setInt(IsDelete.NO.toIntValue());
		param.setString(userEvaluate.getShopId());

		if (StringUtils.isNotBlank(userEvaluate.getGoodsId())) {
			sql.append(" and ue.goods_id=? ");
			param.setString(userEvaluate.getGoodsId());
		}
		if (scoreState != null) {
			switch (scoreState) {
			case 0:
				sql.append(" and score<=? and score>=?");
				param.setDouble(2.5d);
				param.setDouble(1d);
			case 1:
				sql.append(" and score<? and score>?");
				param.setDouble(4d);
				param.setDouble(2.5d);
			case 2:
				sql.append(" and score<=? and score>=?");
				param.setDouble(5d);
				param.setDouble(4d);
			}
		}

		return queryForInt(sql.toString(), param);

	}

	@Override
	public UserEvaluateCountBO findEvaluateCount(String goodsId) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select");
		sql.append(" count(id) count,");
		sql.append(" count(case when score<=? and score>=? then score end) bestCount,");
		sql.append(" count(case when score<? and score>? then score end) middleCount,");
		sql.append(" count(case when score<=? and score>=? then score end) badCount,");
		sql.append(" convert(ifnull(avg(score),0),decimal(4,2)) avgscore");
		sql.append(" from user_evaluate");
		sql.append(" where is_delete=? and goods_id=?");

		StatementParameter param = new StatementParameter();
		param.setDouble(5d);
		param.setDouble(4d);
		param.setDouble(4d);
		param.setDouble(2.5d);
		param.setDouble(2.5d);
		param.setDouble(1d);
		param.setInt(IsDelete.NO.toIntValue());
		param.setString(goodsId);

		return query(sql.toString(), UserEvaluateCountBO.class, param);
	}

	@Override
	public List<UserEvaluateBO> findUserEvaluate(int pageNumber,int maxPageRow,String goodsId,Integer status, String evaluateTagId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select distinct ue.id,ue.shop_id,ue.user_id,ue.goods_id,orders_goods_id,ue.is_anonymity,");
		sql.append("ue.score,ue.image,ue.content,ue.created_time,ue.status,ue.reply,u.nick_name,");
		sql.append("u.avatar_url 'head_img_url',og.created_time 'purchase_time',sc.standards,ue.again_evaluate");
		sql.append(" from user_evaluate ue");
//		sql.append(" inner join goods_evaluate_tag gt on ue.id=gt.user_evaluate_id");
//		sql.append(" inner join evaluate_tag et on et.id=gt.evaluate_tag_id");
		sql.append(" inner join `user` u on u.id=ue.user_id");
		sql.append(" inner join orders_goods og on og.id=ue.orders_goods_id");
		sql.append(" left join sku_content sc on sc.id=og.sku_content_id");
		sql.append(" where ue.is_delete=? and ue.goods_id=?");
		
		StatementParameter param = new StatementParameter();
		param.setInt(IsDelete.NO.toIntValue());
		param.setString(goodsId);
		
//		if(StringUtils.isNotBlank(evaluateTagId)){
//			sql.append(" and et.id=?");
//			param.setString(evaluateTagId);
//		}
		if(status!=null){
			if(status==UserEvaluateSeachStatus.HASIMG.toIntStatus()){
				sql.append(" and ue.image <> ''");
			}else if(status==UserEvaluateSeachStatus.AGAIN.toIntStatus()){
				sql.append(" and ue.status = ?");
				param.setInt(Is.YES.toIntValue());
			}else if(status==UserEvaluateSeachStatus.NEW.toIntStatus()){
				sql.append(" order by ue.created_time desc");
			}
		}
		
		return super.queryForPagingList(sql.toString(), UserEvaluateBO.class, param,pageNumber,maxPageRow);
	}

	@Override
	public Double getShopEvaluateScore(String shopId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select ifnull(avg(score),0) from shop s");
		sql.append(" inner join user_evaluate ue on s.id=ue.shop_id");
		sql.append(" where ue.is_delete=? and s.id=?");
		
		StatementParameter param=new StatementParameter();
		param.setInt(IsDelete.NO.toIntValue());
		param.setString(shopId);
		
		return queryForDouble(sql.toString(), param);
	}

	@Override
	public List<UserEvaluateBO> findWithinRangeUserEvaluate(String goodsId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select distinct ue.id,ue.shop_id,ue.user_id,ue.goods_id,ue.is_anonymity,");
		sql.append("ue.score,ue.image,ue.content,ue.created_time,ue.status,ue.reply,u.nick_name,");
		sql.append(" u.avatar_url 'head_img_url',og.created_time 'purchase_time',sc.standards,ue.again_evaluate ");
		sql.append(" from user_evaluate ue");
		sql.append(" inner join `user` u on u.id=ue.user_id");
		sql.append(" inner join orders_goods og on og.id=ue.orders_goods_id");
		sql.append(" left join sku_content sc on sc.id=og.sku_content_id");
		sql.append(" where ue.is_delete=? and ue.goods_id=?");
		
		StatementParameter param = new StatementParameter();
		param.setInt(IsDelete.NO.toIntValue());
		param.setString(goodsId);
		
		sql.append(" and ue.score = ? ");
		param.setInt(UserEvaluateScore.FIVES.toIntScore());
		sql.append(" and (ue.image = '' or ue.image is null) ");
		sql.append(" and ue.status = ? ");
		param.setInt(Is.NO.toIntValue());
		sql.append(" order by ue.created_time desc ");
		sql.append(" limit 2 ");
		
		return super.queryForList(sql.toString(), UserEvaluateBO.class, param);
	}
	
}
